/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.matrimonybeans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;

/**
 *
 * @author ChauAnh
 */
@Stateless
public class Login implements LoginLocal {

    Connection conn;
    String fullName;
    String avatar;
    @Override
    public Boolean checkExists(String username, String password) {
        try {
            ConnectDB conDB = new ConnectDB();
            conn = conDB.getConnection();
            String strSql = "SELECT * FROM Candidate WHERE username = ? AND password = ?";
            PreparedStatement pstmt = conn.prepareCall(strSql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                fullName = rs.getString("first_name")+" "+rs.getString("middle_name")+" "+rs.getString("last_name"); 
                avatar = rs.getString("avatar");
                return true;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public Boolean checkExistsEmail(String email, String password) {
        try {
            ConnectDB conDB = new ConnectDB();
            conn = conDB.getConnection();
            String strSql = "SELECT * FROM Candidate WHERE email_id = ? AND password = ?";
            PreparedStatement pstmt = conn.prepareCall(strSql);
            pstmt.setString(1, email);
            pstmt.setString(2, password);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                fullName = rs.getString("first_name")+" "+rs.getString("middle_name")+" "+rs.getString("last_name"); 
                avatar = rs.getString("avatar");
                return true;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public Boolean checkExistAdmin(String username, String password) {
        try {
            ConnectDB conDB = new ConnectDB();
            conn = conDB.getConnection();
            String strSql = "SELECT * FROM Adminitrator WHERE username_admin = ? AND password = ?";
            PreparedStatement pstmt = conn.prepareCall(strSql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                fullName = rs.getString("full_name");
                avatar = rs.getString("avatar");
                return true;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public String getFullName() {
        return fullName;
    }

    @Override
    public String getAvatar() {
        return avatar;
    }

    @Override
    public Boolean registerAccount(String username, String email, String pass, String fname, String mname, String lname, String gender, String maritalStatus) {
        ConnectDB conDB = new ConnectDB();
        try {
            conn = conDB.getConnection();            
            String registerSql = "insert into Candidate(username,email_id,password,first_name,middle_name,last_name,gender,marital_status) values(?,?,?,?,?,?,?,?);";
            PreparedStatement pstm = conn.prepareStatement(registerSql);
            pstm.setString(1, username);
            pstm.setString(2, email);
            pstm.setString(3, pass);
            pstm.setString(4, fname);
            pstm.setString(5, mname);
            pstm.setString(6, lname);
            pstm.setString(7, gender);
            pstm.setString(8, maritalStatus);
            pstm.executeQuery();
            
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    @Override
    public Boolean checkEmail(String email) {
        ConnectDB conDB = new ConnectDB();
        try {
            conn = conDB.getConnection();
            String emailSql = "select email_id from Candidate where email_id = ?";
            PreparedStatement pstm = conn.prepareCall(emailSql);
            pstm.setString(1, email);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                return false;
            }
            
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    @Override
    public Boolean checkUser(String username) {
        ConnectDB conDB = new ConnectDB();
        try {
            conn = conDB.getConnection();
            String userSql = "select username from Candidate where username = ?";
            PreparedStatement pstm = conn.prepareCall(userSql);
            pstm.setString(1, username);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                return false;
            }
            
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
    
    
    
    
}
